package DataBase;

import java.sql.Array;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Types;
import java.util.Vector;

/*
 * Class that get range of date and send query to DB for request Waiters's Report.It return waiters's report to client(task assignment).
 * Used by MessageGetReportWaitersByDate and MessageGetReportWaitersByDateReply
 */

public class GetWaitersReportByDate {

	private String fromDate;
	private String toDate;
	private Vector<Vector> report;
	private Vector<String>  row;
	private Vector<String>  listWaitings;
	private String num_tables;
	
	public GetWaitersReportByDate(String fromDate,String toDate)
	{
		this.fromDate=fromDate;
		this.toDate=toDate;
		
	}

	
	public Vector<Vector> getReportOfWaiters ()
	{
		
        SQLConnection sc=new SQLConnection();
		
		     
        	try {	
        		/*
        		 * ID of all waiters
        		 */
        		PreparedStatement ps = sc.getConnection().prepareStatement("SELECT eid FROM employee WHERE role='מלצר'  ;");	
        		ResultSet rs=ps.executeQuery();	
        		ResultSetMetaData rsmd = rs.getMetaData();
        		report = new Vector<Vector>();
        		listWaitings = new Vector<String>();
        		while (rs.next())
        		{
        			for(int col = 1; col <= rsmd.getColumnCount(); col++) 
		         	{                      	                
        				int type = rsmd.getColumnType(col);
    	                switch(type)
    	                {
    	                
    	                    case Types.INTEGER :
    	                    	listWaitings.add(new Integer(rs.getInt(col)).toString());
    	                        break;
    	                        
    	                    case Types.VARCHAR :
    	                    	listWaitings.add(rs.getString(col));
    	                        break;
    	                    case Types.DATE :
    	                    	listWaitings.add(rs.getDate(col).toString());
    	                        break;    
    	                }//switch
                    }
        		}
        		        		        		
        		 for(int i=0;i<listWaitings.size();i++)
        		 {
        		 
        		 
        		 /*
        		  * Quantity of all table that get service from this waiter
        		  */
        		 PreparedStatement ps1 = sc.getConnection().prepareStatement("SELECT COUNT(eid) FROM employee_table_timeslot " +
        		 		                                                     "where eid=? AND dateOpen>=? AND dateOpen<?");	
        		 /*
        		  * Quantity of all  persons that get service from this waiter 
        		  */
        		 PreparedStatement ps2 = sc.getConnection().prepareStatement("SELECT SUM(no_of_persons)  FROM table_timeslot tt," +  
        		 		                                                     "employee_table_timeslot ett WHERE  tt.dateOpen=ett.dateOpen " +
        		 		                                                     "AND tt.tid=ett.tid AND ett.eid=? and ett.dateOpen>=? " +
        		 		                                                     "AND ett.dateOpen<?");
        		 /*
        		  * Names of waiters
        		  */
        		 PreparedStatement ps3 = sc.getConnection().prepareStatement("SELECT name FROM employee WHERE eid=?");	
        		 /*
        		  * Quantity of this menu
        		  */
        		 
        		 PreparedStatement ps4 = sc.getConnection().prepareStatement("SELECT COUNT(mid) FROM order_from_a_table oft,employee_table_timeslot ett" +
        		 		                                                      " where oft.dateOpen=ett.dateOpen AND oft.timeOpen=ett.timeOpen AND ett.eid=? AND oft.dateOpen>=? AND" +
        		 		                                                      " oft.dateOpen<?  AND oft.mid='1'");	
        		 
        		          		 
         		 ps1.setString(1, listWaitings.get(i));
     			 ps1.setString(2,fromDate);
     			 ps1.setString(3,toDate);
     			 ps2.setString(1, listWaitings.get(i));
     			 ps2.setString(2,fromDate);
     			 ps2.setString(3,toDate);
     			 ps3.setString(1, listWaitings.get(i));
     			 ps4.setString(1, listWaitings.get(i));
     			 ps4.setString(2,fromDate);
    			 ps4.setString(3,toDate);
    			 
     			 ResultSet rs1=ps1.executeQuery();	
     			 ResultSet rs2=ps2.executeQuery();
     			 ResultSet rs3=ps3.executeQuery();	
     			 ResultSet rs4=ps4.executeQuery();	  
     			 
      			while(rs2.next() && rs1.next() && rs3.next() && rs4.next())
     	        {   
      				row = new Vector<String>();
      				row.add(listWaitings.get(i));
      				row.add(rs3.getString(1));
      				row.add(rs1.getString(1));
      				if(rs2.getString(1)!=null) 
          				row.add(rs2.getString(1));
          			else
          				row.add(Integer.toString(0));	
      				row.add(rs4.getString(1));
      			    report.add(row);
      			     				
     	        }   //while
        		 }  //for
      			
        		 sc.getConnection().close();
      			
        		 
        		} 
        	
        	    catch (SQLException e) {
        			// TODO Auto-generated catch block
        			e.printStackTrace();
        		}
        	
        		return report;
        	
        }
        
       

	}
	
	
